package com.hbcx;

import com.alibaba.fastjson2.JSONObject;
import com.hbcx.dto.ColumnConfigItem;
import com.hbcx.dto.TableConfig;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Hello world!
 *
 */
public class App 
{
    public static Map<String,String> config = new HashMap<>();
    private static Logger logger = LogManager.getLogger(App.class.getName());
    public static TableConfig tableConfig;
    public static void main( String[] args ) throws ClassNotFoundException, SQLException, IOException {
        if(args.length<2)
        {
            logger.info("用法: java -jar app.jar <config json file> <xlsx file> \n");
            return;
        }

        getDataSourceConfig("config.conf");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = config.get("url");
        String user = config.get("user");
        String password = config.get("password");
//        TableConfig tableConfig = getTableConfigByFile("kkqk.json");
        String jsonFileName = args[0];
        String xlsxFileName = args[1];
        tableConfig = getTableConfigByFile(jsonFileName);
        logger.info("======开始导入{}======",xlsxFileName);

//        List<Map<String,Object>> list = getTableData("/home/geloor/Projects/SheetToDatabase/模板_055830/650102_总会计库款情况1.xlsx",tableConfig);
        List<Map<String,Object>> list = getTableData(xlsxFileName,tableConfig);
        //设置超时时间
        int timeOutSeconds = Integer.parseInt(config.get("timeOutSeconds")==null?"2":config.get("timeOutSeconds"));
        DriverManager.setLoginTimeout(timeOutSeconds);
        Connection connection = DriverManager.getConnection(url,user,password);
        connection.setAutoCommit(false);
        logger.info("开始导入数据，请耐心等待...");
        StringBuilder sql= null;
        PreparedStatement preparedStatement = null;
        List<String> columns=new ArrayList<>();

        if(sql == null)
        {
            sql=new StringBuilder("insert into "+tableConfig.getTableName()+" (");
            StringBuilder placeHolder=new StringBuilder();
            for (int i = 0; i < tableConfig.getColumnConfig().length; i++) {
                ColumnConfigItem item=tableConfig.getColumnConfig()[i];
                putColumnToSql(item,sql,placeHolder,columns);
                if(item.getRelationColumn()!=null && item.getRelationColumn().length>0)
                {
                    for (int j = 0; j < item.getRelationColumn().length; j++) {
                        putColumnToSql(item.getRelationColumn()[j],sql,placeHolder,columns);
                    }
                }
            }
            sql.deleteCharAt(sql.length()-1);
            placeHolder.deleteCharAt(placeHolder.length() - 1);
            sql.append(" ) Values (").append(placeHolder.toString()).append(")");
            System.out.println(sql.toString());
            preparedStatement = connection.prepareStatement(sql.toString());
        }
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> map = list.get(i);
            int ind=0;
            for (int i1 = 0; i1 < columns.size(); i1++) {
                preparedStatement.setObject(++ind,map.getOrDefault(columns.get(i1),null));
            }
            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();
        preparedStatement.close();
        connection.commit();
        connection.close();
        logger.info("======导入{}结束======",xlsxFileName);
    }
    public static void getDataSourceConfig(String fileName) throws IOException {
        try (BufferedReader reader = new BufferedReader(new FileReader(fileName))) {
            String line;
            while ((line = reader.readLine()) != null) {
                // 打印每一行
                String[] blocks = line.split("=");
                if(!line.trim().startsWith("#") && blocks.length == 2)
                {
                    config.put(blocks[0].trim(), blocks[1].trim());
                }
            }
        }catch (IOException e) {
            throw e;
        }
    }

    public static void  putColumnToSql(ColumnConfigItem item,StringBuilder sql,StringBuilder placeHolder,List<String> column)
    {
        if(!item.isOnlyFlag())
        {

            if(item.getColumnType()==null) {
                if(column.contains(item.getColumn()))
                {
                    return;
                }else
                {
                    column.add(item.getColumn());
                }
                sql.append(item.getColumn()).append(",");
                placeHolder.append("?,");
            }else if(item.getColumnType().equals("guid"))
            {
                sql.append(item.getColumn()).append(",");
                placeHolder.append(ColumnConfigItem.COLUMN_TYPE.GETGUID+",");
            }else if(item.getColumnType().equals("sysdate"))
            {
                sql.append(item.getColumn()).append(",");
                placeHolder.append(ColumnConfigItem.COLUMN_TYPE.SYSDATA+",");
            }else
            {
                if(column.contains(item.getColumn()))
                {
                    return;
                }else
                {
                    column.add(item.getColumn());
                }
                sql.append(item.getColumn()).append(",");
                placeHolder.append("?,");
            }
        }
    }

    public static TableConfig getTableConfigByFile(String fileName){
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(Files.newInputStream(Paths.get(fileName)), StandardCharsets.UTF_8))) {
            String line;
            StringBuilder sb = new StringBuilder();
            while ((line = reader.readLine()) != null) {
                sb.append(line);
            }
            JSONObject jsonObject = JSONObject.parseObject(sb.toString());
            TableConfig tc = jsonObject.toJavaObject(TableConfig.class);
            return tc;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    public static List<Map<String, Object>> getTableData(String xlsxFileName, TableConfig tableConfig) throws IOException {
        File file = new File(xlsxFileName);
        if(!file.exists())
        {
            throw new RuntimeException("文件名或者路径有问题，请检查："+ xlsxFileName +"是否正确！");
        }
        if(file.isDirectory())
        {
            List<Map<String, Object>> result = new ArrayList<>();
            String[] files = file.list();
            if(files!=null)
            {
                List<String> xlsxFiles = Arrays.stream(files).filter(t->t.endsWith(".xlsx")).collect(Collectors.toList());
                for (String xlsxFile : xlsxFiles) {
                    logger.info("读取文件："+xlsxFile);
                    result.addAll(getTableDataByFile(xlsxFileName+"/"+xlsxFile, tableConfig));
                }
            }
            return result;
        }else
        {
            return getTableDataByFile(xlsxFileName, tableConfig);
        }
    }
    public static List<Map<String, Object>> getTableDataByFile(String xlsxFileName, TableConfig tableConfig) throws IOException {
        List<Map<String, Object>> result = new LinkedList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(xlsxFileName);
        XSSFSheet sheet =  workbook.getSheetAt(tableConfig.getSheetIndex());
        int startRow = tableConfig.getStartRow();
        ColumnConfigItem[] columns = tableConfig.getColumnConfig();
        for(int i=startRow-1;i<=sheet.getLastRowNum();i++)
        {
            Map<String,Object> map = new LinkedHashMap<>();
            List<Map<String,Object>> list = new ArrayList<>();
            XSSFRow row = sheet.getRow(i);
            if(row.getLastCellNum()<=0||isBlankRow(row))
            {
                continue;
            }
            for (int j = 0; j < columns.length; j++) {
                ColumnConfigItem item = columns[j];
                if(item.getColumnType()==null || item.getColumnType().isEmpty())
                {
                    int index = convertToIndex(item.getColumnRef());
                    XSSFCell cell =  row.getCell(index);
                    if(cell!=null)
                    {
                        map.put(item.getColumn(),getValue(cell,item));
                    }
                }else if(item.getColumnType()!=null)
                {
                    if(item.getColumnType().trim().equalsIgnoreCase("guid"))
                    {
                        map.put(item.getColumn(), ColumnConfigItem.COLUMN_TYPE.GETGUID);
                    }else if(item.getColumnType().trim().equalsIgnoreCase("sysdate")){
                        map.put(item.getColumn(), ColumnConfigItem.COLUMN_TYPE.SYSDATA);
                    }else if(item.getColumnType().trim().equalsIgnoreCase("string")){
                        map.put(item.getColumn(),item.getColumnRef());
                    }else if(item.getColumnType().trim().equalsIgnoreCase("stringref")){
                        String ref=item.getColumnRef();
                        int colI=getCol(ref);
                        int rowI=getRow(ref);
                        Object value = getValue(row.getSheet().getRow(rowI).getCell(colI),item);
                        map.put(item.getColumn(), value);
                    }
                    else if(item.getColumnType().trim().equalsIgnoreCase("H_Expand")){
                        list.addAll(getHExpandData(map,item,row));
                    }
                }
            }
            if(!list.isEmpty())
            {
                result.addAll(list);
            }else
            {
                result.add(map);
            }
        }
        return result;
    }
    public static boolean isBlankRow(XSSFRow row)
    {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            if(row.getCell(i)!=null && row.getCell(i).getRawValue()!=null && !row.getCell(i).getRawValue().trim().isEmpty())
                return false;
        }
        return true;
    }
    public static List<Map<String,Object>> getHExpandData(Map<String,Object> oriMap,ColumnConfigItem columnItem,XSSFRow row)
    {
        List<Map<String,Object>> result = new LinkedList<>();
        int startColInx = convertToIndex(columnItem.getStartRef());
        int endColInx = -1;
        if(columnItem.getEndRef()!=null && !columnItem.getEndRef().isEmpty())
        {
            endColInx = convertToIndex(columnItem.getEndRef());
        }
        endColInx = endColInx==-1?(row.getLastCellNum()-1):endColInx;
        for (int i = startColInx; i <= endColInx; i++) {
            Map<String,Object> map = new LinkedHashMap<>(oriMap);
            map.put(columnItem.getColumn(),getValue(row.getCell(i),columnItem));
            boolean abandonsFlag = false;
            for (int j = 0; j < columnItem.getRelationColumn().length; j++) {
                ColumnConfigItem subColumnItem = columnItem.getRelationColumn()[j];
                int rowIndex = subColumnItem.getRowIndex()-1;
                Object value= getValueOfMergeCell(row.getSheet().getRow(rowIndex).getCell(i),subColumnItem);
                if(subColumnItem.getExceptString()!=null || subColumnItem.isExceptEmpty())
                {
                    if(subColumnItem.isExceptEmpty())
                    {
                        if(value==null || value.toString().trim().isEmpty())
                        {
                            abandonsFlag=true;
                        }
                    }
                    //排除一些特定的字符串
                    if(subColumnItem.getExceptString()!=null && subColumnItem.getExceptString().length>0)
                    {

                        if(value!=null &&
                                Arrays.stream(subColumnItem.getExceptString())
                                        .anyMatch(t->t.equals(value.toString().trim())))
                        {
                            abandonsFlag=true;
                        }
                    }
                }
                if(!subColumnItem.isOnlyFlag())
                {
                    map.put(subColumnItem.getColumn(),value);
                }
            }
            if(!abandonsFlag)
            {
                result.add(map);
            }
        }
        return result;
    }
    public static Object getValueOfMergeCell(XSSFCell cell,ColumnConfigItem item)
    {
        Object result = getXSSFCellValue(cell,true);
        if(item.isUseAmtScale() && result instanceof Number)
        {
            Number m=(Number) result;
            m = (new BigDecimal(m.toString())).multiply(BigDecimal.valueOf(tableConfig.getAmtScale()));
            result = m;
        }
        return result;
    }
    public static Object getValue(XSSFCell cell,ColumnConfigItem item)
    {
        Object result = getXSSFCellValue(cell);
        if(item.isUseAmtScale() && result instanceof Number)
        {
           Number m=(Number) result;
           m = (new BigDecimal(m.toString())).multiply(BigDecimal.valueOf(tableConfig.getAmtScale()));
           result = m;
        }
        return result;
    }
    public static Object getXSSFCellValue(XSSFCell cell,boolean checkMerge)
    {
        if(checkMerge)
        {
            XSSFCell finalCell = cell;
            Optional<CellRangeAddress> belongRegion = cell.getSheet().getMergedRegions().stream()
                    .filter(t->t.containsRow(finalCell.getRowIndex())&& t.containsColumn(finalCell.getColumnIndex()))
                    .findFirst();
            if(belongRegion.isPresent())
            {
                cell = cell.getSheet().getRow(belongRegion.get().getFirstRow()).getCell(belongRegion.get().getFirstColumn());
            }
        }
        return getXSSFCellValue(cell);
    }
    public static Object getXSSFCellValue(XSSFCell cell){
        Object cellValue = null;
        //1.3、获取单元格数据的类型
        switch (cell.getCellType()) {
            case NUMERIC:
                //1.3.1、获取到单元格数据的格式
                short dataFormat = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //1.3.1.1、处理日期格式，根据不同日期长度去判断
                    switch (dataFormat) {
                        case 14:
                            sdf = new SimpleDateFormat("yyyy/MM/dd");
                            break;
                        case 21:
                            sdf = new SimpleDateFormat("HH:mm:ss");
                            break;
                        case 22:
                            sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                            break;
                    }
                    //1.3.1.2、处理时间格式
                    Date date = cell.getDateCellValue();
                    assert sdf != null;
                    cellValue = sdf.format(date);
                } else{
                    double numericCellValue = cell.getNumericCellValue();
                    cellValue = numericCellValue;
                }
                break;
            case STRING:
            case BLANK:
                //处理字符串类型
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                //处理布尔类型
                cellValue = cell.getBooleanCellValue();
                break;
            case FORMULA:
                //处理函数类型
                cellValue = cell.getCellFormula();
                break;
            case ERROR:
                cellValue = cell.getErrorCellValue();;
                break;
            default:
                break;
        }
        return cellValue;
    }
    public static int convertToIndex(String columnName) {
        int number = 0;
        for (int i = 0; i < columnName.length(); i++) {
            char c = columnName.charAt(i);
            number = number * 26 + (c - 'A' + 1);
        }
        return number - 1; // 因为Excel列是从1开始的，而我们需要的是从0开始的数字
    }
    public static int getRow(String cell) {
        String numberPart = cell.replaceAll("[^0-9]", "");
        return Integer.parseInt(numberPart)-1;
    }

    public static int getCol(String cell) {
        String letterPart = cell.replaceAll("[^A-Z]", "");
        return convertToIndex(letterPart);
    }
}
